﻿/*
    Copyright (c) 2012, Matías López
    All rights reserved.

    Redistribution and use in source and binary forms, with or without modification, are permitted 
    provided that the following conditions are met:
        *   Redistributions of source code must retain the above copyright notice, this list of 
            conditions and the following disclaimer.
        *   Redistributions in binary form must reproduce the above copyright notice, this list of 
            conditions and the following disclaimer in the documentation and/or other materials 
            provided with the distribution.
        *   Neither the name of Matías López nor the names of its contributors may be used to 
            endorse or promote products derived from this software without specific prior written 
            permission.
    
    THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR 
    IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY 
    AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR 
    CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR 
    CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR 
    SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY 
    THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR 
    OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
    POSSIBILITY OF SUCH DAMAGE.
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using mana.Data.Sql.Schema;

namespace mana.Data.Sql
{
    public class SqlCmdSelect
    {
        public SqlCmdSelect(SqlTableSchema table)
            : this(table.FormattedName)
        {
            SelectFields = table.ColumnsFormattedNamesString;
        }

        public SqlCmdSelect(string tableName)
        {
            TableName = tableName;

            WhereExpression = new SqlCmdWhereExpression();
            OrderByExpression = new SqlCmdOrderByExpression();
        }

        #region Properties

        #region Public

        public string TableName { get; private set; }
        public int? Top { get; set; }
        public SqlCmdWhereExpression WhereExpression { get; private set; }
        public SqlCmdOrderByExpression OrderByExpression { get; set; }
        public string SelectFields { get; set; }

        public IEnumerable<SqlParameter> Parameters
        {
            get { return WhereExpression.Parameters; }
        }

        public string Cmd
        {
            get
            {
                if (string.IsNullOrWhiteSpace(SelectFields))
                    SelectFields = "*";

                if (!LastRowNumber.HasValue)
                {
                    string topExpression = string.Empty;
                    if (Top.HasValue)
                        topExpression = "TOP " + Top.Value;

                    return string.Format("SELECT {0} {1} FROM {2} {3} {4}", topExpression, SelectFields, TableName, WhereExpression, OrderByExpression);
                }

                if (string.IsNullOrWhiteSpace(OrderByExpression.Expression))
                    throw new Exception("OrderBy expressions must be declared for the pagination to work.");

                return string.Format(
    @"WITH [Result] AS
(
    SELECT TOP ({0}) {1}, ROW_NUMBER() OVER ({2}) AS RowNumber
    FROM {3}
    {4}
)
SELECT {1}
FROM [Result]
WHERE RowNumber BETWEEN {5} AND {6}
", LastRowNumber, SelectFields, OrderByExpression, TableName, WhereExpression, StartRowIndex + 1, LastRowNumber);
            }
        }

        public string CountCmd
        {
            get { return string.Format("SELECT COUNT(1) FROM {0} {1}", TableName, WhereExpression); }
        }

        #endregion Public

        #region Private

        private int? StartRowIndex { get; set; }
        private int? MaximumRows { get; set; }

        private int? LastRowNumber
        {
            get
            {
                if (StartRowIndex.HasValue && MaximumRows.HasValue)
                {
                    if (StartRowIndex.Value == 0)
                        return MaximumRows.Value;

                    return StartRowIndex.Value + MaximumRows.Value;
                }


                return null;
            }
        }

        #endregion Private

        #endregion Properties

        #region Methods

        /// <summary>
        /// Sets the query's pagination data.
        /// </summary>
        /// <param name="startRowIndex">The initial row number to return (0-based number).</param>
        /// <param name="maximumRows">The maximum rows number to return.</param>
        public void SetPagination(int startRowIndex, int maximumRows)
        {
            if (startRowIndex < 0)
                throw new ArgumentOutOfRangeException("startRowIndex", startRowIndex, "The start row index must be greater than or equal to 0.");
            if (maximumRows < 1)
                throw new ArgumentOutOfRangeException("maximumRowCount", maximumRows, "The maximum rows number must be greater than or equal to 1.");

            StartRowIndex = startRowIndex;
            MaximumRows = maximumRows;
        }

        public override string ToString()
        {
            return Cmd;
        }

        #endregion Methods
    }

    public class SqlCmdInsert
    {
        public SqlCmdInsert(SqlTableSchema table)
            : this(table.FormattedName)
        { }

        public SqlCmdInsert(string tableName)
        {
            TableName = tableName;

            FieldsAndValuesExpression = new SqlCmdFieldsAndValuesExpression();
        }

        #region Properties

        public string TableName { get; private set; }
        public SqlCmdFieldsAndValuesExpression FieldsAndValuesExpression { get; private set; }

        public IEnumerable<SqlParameter> Parameters
        {
            get { return FieldsAndValuesExpression.Parameters; }
        }

        public string Cmd
        {
            get { return string.Format("INSERT INTO {0} {1}", TableName, FieldsAndValuesExpression.Expression); }
        }

        #endregion Properties

        #region Methods

        #region Public

        public override string ToString()
        {
            return Cmd;
        }

        #endregion Public

        #endregion Methods
    }

    public class SqlCmdUpdate
    {
        public SqlCmdUpdate(SqlTableSchema table)
            : this(table.FormattedName)
        { }

        public SqlCmdUpdate(string tableName)
        {
            TableName = tableName;

            SetExpression = new SqlCmdSetExpression();
            WhereExpression = new SqlCmdWhereExpression();
        }

        #region Properties

        public string TableName { get; private set; }
        public SqlCmdSetExpression SetExpression { get; private set; }
        public SqlCmdWhereExpression WhereExpression { get; private set; }

        public IEnumerable<SqlParameter> Parameters
        {
            get { return SetExpression.Parameters.Union(WhereExpression.Parameters); }
        }

        public string Cmd
        {
            get { return string.Format("UPDATE {0} SET {1} {2}", TableName, SetExpression, WhereExpression); }
        }

        #endregion Properties

        #region Methods

        #region Public

        public override string ToString()
        {
            return Cmd;
        }

        #endregion Public

        #endregion Methods
    }

    public class SqlCmdDelete
    {
        public SqlCmdDelete(SqlTableSchema table)
            : this(table.FormattedName)
        { }

        public SqlCmdDelete(string tableName)
        {
            TableName = tableName;

            WhereExpression = new SqlCmdWhereExpression();
        }

        #region Properties

        public string TableName { get; private set; }
        public SqlCmdWhereExpression WhereExpression { get; private set; }

        public IEnumerable<SqlParameter> Parameters
        {
            get { return WhereExpression.Parameters; }
        }

        public string Cmd
        {
            get
            {
                return string.Format("DELETE {0} {1}", TableName, WhereExpression);
            }
        }

        #endregion Properties

        #region Methods

        #region Public

        public override string ToString()
        {
            return Cmd;
        }

        #endregion Public

        #endregion Methods
    }

    #region Utils

    public class PaginationAndSortingOptions
    {
        public int? StartRowIndex { get; set; }
        public int? MaximumRows { get; set; }
        public bool IsPaginationSet
        {
            get { return StartRowIndex.HasValue && MaximumRows.HasValue; }
        }
        public string SortByExpression { get; set; }
        public bool IsSortingSet
        {
            get { return !string.IsNullOrWhiteSpace(SortByExpression); }
        }
        public int TotalRowCount { get; set; }
    }

    public class SqlCmdColumnWithValue
    {
        public SqlCmdColumnWithValue(SqlColumnSchema column, object value)
        {
            Column = column;
            Value = value;
        }

        public SqlColumnSchema Column { get; private set; }
        public object Value { get; private set; }
    }

    public class SqlCmdSetExpression
    {
        private List<string> _values;
        private List<SqlParameter> _parameters;

        public SqlCmdSetExpression()
        {
            _values = new List<string>();
            _parameters = new List<SqlParameter>();
        }

        #region Properties

        public string Expression
        {
            get { return string.Join(", ", _values); }
        }

        public IEnumerable<SqlParameter> Parameters
        {
            get { return _parameters; }
        }

        #endregion Properties

        #region Methods

        #region Public

        public void Add(SqlColumnSchema column, object value)
        {
            Add(new SqlCmdColumnWithValue(column, value));
        }

        public void Add(SqlCmdColumnWithValue columnWithValue)
        {
            string parameterName = AddParameter(columnWithValue.Value);
            _values.Add(string.Format("{0} = {1}", columnWithValue.Column, parameterName));
        }

        public void AddRange(IEnumerable<SqlCmdColumnWithValue> columnsWithValues)
        {
            foreach (var column in columnsWithValues)
            {
                Add(column);
            }
        }

        public override string ToString()
        {
            return Expression;
        }

        #endregion Public

        #region Private

        /// <summary>
        /// Adds the parameter to the inner list and returns the parameter name.
        /// </summary>
        /// <param name="parameterValue"></param>
        /// <returns></returns>
        private string AddParameter(object parameterValue)
        {
            string parameterName = string.Format("@pS{0}", _parameters.Count);
            _parameters.Add(new SqlParameter(parameterName, parameterValue));
            return parameterName;
        }

        #endregion Private

        #endregion Methods
    }

    public class SqlCmdFieldsAndValuesExpression
    {
        private List<string> _fields;
        private List<string> _values;
        private List<SqlParameter> _parameters;

        public SqlCmdFieldsAndValuesExpression()
        {
            _fields = new List<string>();
            _values = new List<string>();
            _parameters = new List<SqlParameter>();
        }

        #region Properties

        public string Expression
        {
            get { return string.Format("({0}) VALUES ({1})", string.Join(", ", _fields), string.Join(", ", _values)); }
        }

        public IEnumerable<SqlParameter> Parameters
        {
            get { return _parameters; }
        }

        #endregion Properties

        #region Methods

        #region Public

        public void Add(SqlColumnSchema column, object value)
        {
            Add(new SqlCmdColumnWithValue(column, value));
        }

        public void Add(SqlCmdColumnWithValue columnWithValue)
        {
            string parameterName = AddParameter(columnWithValue.Value);
            _fields.Add(columnWithValue.Column.FormattedName);
            _values.Add(parameterName);
        }

        public void AddRange(IEnumerable<SqlCmdColumnWithValue> columnsWithValues)
        {
            foreach (var column in columnsWithValues)
            {
                Add(column);
            }
        }

        public override string ToString()
        {
            return Expression;
        }

        #endregion Public

        #region Private

        /// <summary>
        /// Adds the parameter to the inner list and returns the parameter name.
        /// </summary>
        /// <param name="parameterValue"></param>
        /// <returns></returns>
        private string AddParameter(object parameterValue)
        {
            string parameterName = string.Format("@pFV{0}", _parameters.Count);
            _parameters.Add(new SqlParameter(parameterName, parameterValue));
            return parameterName;
        }

        #endregion Private

        #endregion Methods
    }

    public class SqlCmdWhereExpression
    {
        #region Properties

        public string Expression { get; private set; }
        public List<SqlParameter> Parameters { get; private set; }

        #endregion Properties

        #region Methods

        #region Public

        public void Set(string expression, List<SqlParameter> parameters)
        {
            Expression = expression;
            Parameters = parameters;
        }

        public override string ToString()
        {
            if (string.IsNullOrWhiteSpace(Expression))
                return string.Empty;

            return "WHERE " + Expression;
        }

        #endregion Public

        #endregion Methods
    }

    public class SqlCmdOrderByExpression
    {
        private List<string> _values;

        public SqlCmdOrderByExpression()
        {
            _values = new List<string>();
        }

        #region Properties

        /// <summary>
        /// Order expression.
        /// </summary>
        public string Expression
        {
            get
            {
                if (_values.Count == 0)
                    return string.Empty;

                return "ORDER BY " + string.Join(", ", _values);
            }
        }

        #endregion Properties

        #region Methods

        #region Public

        /// <summary>
        /// Adds a new order expression using the specified column.
        /// </summary>
        /// <param name="column">Column name.</param>
        public void Add(SqlColumnSchema column)
        {
            Add(column, true);
        }

        /// <summary>
        /// Adds a new order expression using the specified column and direction.
        /// </summary>
        /// <param name="column">Column name.</param>
        /// <param name="ascending">Order direction.</param>
        public void Add(SqlColumnSchema column, bool ascending)
        {
            _values.Add(string.Format("{0} {1}", column, ascending ? "ASC" : "DESC"));
        }

        /// <summary>
        /// Adds a new order expression.
        /// </summary>
        /// <param name="orderByExpression"></param>
        public void Add(string orderByExpression)
        {
            _values.Add(orderByExpression);
        }

        /// <summary>
        /// Clears all the current order expressions and sets the specified order expression.
        /// </summary>
        /// <param name="orderByExpression"></param>
        public void Set(string orderByExpression)
        {
            Clear();
            Add(orderByExpression);
        }

        /// <summary>
        /// Clears all the order expressions.
        /// </summary>
        public void Clear()
        {
            _values.Clear();
        }

        public override string ToString()
        {
            return Expression;
        }

        #endregion Public

        #endregion Methods
    }

    #endregion Utils
}
